<?php
/*
Plugin Name: WP-FTS
Plugin URI: http://www.davidemauri.it
Description: Wordpress SQL Server Full Text Search 
Author: Davide Mauri
Version: 1.0.1
Author URI: http://www.davidemauri.it/
Contributor: 
*/
$version = "1.0.1";

add_action('admin_menu', 'fts_admin_menu');

register_activation_hook(__FILE__,'fts_activate');

add_filter('posts_join', 'fts_searchjoin' );
add_filter('posts_where', 'fts_where' );
add_filter('posts_orderby', 'fts_orderby' );

function fts_activate() {
	$fts_options = array (
		'fts_enabled' => '0',
		'fts_mode' => 'CONTAINS'
	);

	// Create the required options...
	foreach ($fts_options as $name => $val) {
		add_option($name,$val);
	}
}

function fts_admin_menu()
{
		add_options_page('WP-FTS', 'WP-FTS', 'manage_options', __FILE__, 'fts_option_page');
}

function fts_option_page()
{
	global $wpdb, $version;
	$body = "";
	$action = "none";
	
	$body .= "<h3>Wordpress Table</h3>";
	$body .= "Post Table: " . $wpdb->posts . "<br/>";
	
	$body .= "<h3>Wordpress Options</h3>";
	$body .= "fts_enabled: " . get_option("fts_enabled") . "<br/>";
	$body .= "fts_mode: " . get_option("fts_mode") . "<br/>";
	
	$body .= "<h3>FullText</h3>";
	
	$row = $wpdb->get_row("select IsFulltextInstalled = FULLTEXTSERVICEPROPERTY('IsFulltextInstalled')");
	$fts_installed = $row->IsFulltextInstalled;
	
	if ($fts_installed != "1")
	{
		$body .= "FullText Service is <b>NOT</b> installed! " . $fts_installed;
	}
	else
	{		
		$row = $wpdb->get_row("select catalog_name = fc.name, item_count = FULLTEXTCATALOGPROPERTY(fc.name, 'ItemCount'), change_tracking_state_desc, has_crawl_completed, crawl_type_desc, crawl_start_date, crawl_end_date from sys.fulltext_indexes fi inner join sys.fulltext_catalogs fc on fi.fulltext_catalog_id = fc.fulltext_catalog_id where fi.object_id = object_id('" . $wpdb->posts . "')");
		
		if (empty($row))
		{
			update_option("fts_enabled", "0");

			if (isset($_POST['fts_action']) && $_POST['fts_action'] == "Create Catalog") 
			{
				//Comments in query are needed in order to avoid rewriting by the wp-db-abstraction layer.
				$wpdb->query("/* WP-FTS */ CREATE FULLTEXT CATALOG [" . $wpdb->posts . "] WITH ACCENT_SENSITIVITY = OFF AUTHORIZATION [dbo]");
				$wpdb->query("/* WP-FTS */ CREATE FULLTEXT INDEX ON [dbo].[" . $wpdb->posts . "] KEY INDEX [" . $wpdb->posts . "_ID] ON ([" . $wpdb->posts . "]) WITH (CHANGE_TRACKING AUTO)");
				$wpdb->query("/* WP-FTS */ ALTER FULLTEXT INDEX ON [dbo].[" . $wpdb->posts . "] ADD ([post_content] LANGUAGE [Italian])");
				$wpdb->query("/* WP-FTS */ ALTER FULLTEXT INDEX ON [dbo].[" . $wpdb->posts . "] ADD ([post_content_filtered] LANGUAGE [Italian])");
				$wpdb->query("/* WP-FTS */ ALTER FULLTEXT INDEX ON [dbo].[" . $wpdb->posts . "] ADD ([post_excerpt] LANGUAGE [Italian])");
				$wpdb->query("/* WP-FTS */ ALTER FULLTEXT INDEX ON [dbo].[" . $wpdb->posts . "] ADD ([post_name] LANGUAGE [Italian])");
				$wpdb->query("/* WP-FTS */ ALTER FULLTEXT INDEX ON [dbo].[" . $wpdb->posts . "] ADD ([post_title] LANGUAGE [Italian])");
				$wpdb->query("/* WP-FTS */ ALTER FULLTEXT INDEX ON [dbo].[" . $wpdb->posts . "] ENABLE");
				
				$row = $wpdb->get_row("select catalog_name = fc.name, item_count = FULLTEXTCATALOGPROPERTY(fc.name, 'ItemCount'), change_tracking_state_desc, has_crawl_completed, crawl_type_desc, crawl_start_date, crawl_end_date from sys.fulltext_indexes fi inner join sys.fulltext_catalogs fc on fi.fulltext_catalog_id = fc.fulltext_catalog_id where fi.object_id = object_id('" . $wpdb->posts . "')");

				$action = "Refresh";
			} else {				
				$body .= "FullText Catalog has not been yet created for this site.";				
				$action = "Create Catalog";				
			}
		}
		
		if (!empty($row))
		{
			update_option("fts_enabled", "1");
			update_option("fts_mode", "CONTAINS");

			$body .= "FullText Status: <b>Active</b><br/>";
			$body .= "Catalog Name: " . $row->catalog_name . "<br/>";
			$body .= "Item Count: " . $row->item_count . "<br/>";
			$body .= "Change Tracking State: " . $row->change_tracking_state_desc . "<br/>";
			$body .= "Crawl Completed: " . $row->has_crawl_completed . "<br/>";
			$body .= "Crawl Type: " . $row->crawl_type_desc . "<br/>";
			$body .= "Crawl Start Date: " . $row->crawl_start_date . "<br/>";
			$body .= "Crawl End Date: " . $row->crawl_end_date . "<br/>";
		}
	}
?>
	<div class="wrap">
		<form method="post" id="wp_fts_option_page_form">
			<h2>SQL Server Full Text Plugin v. <?php echo $version ?></h2>
			<p>
			<?php echo $body ?>
			</p>
			<?php if ($action != "none") { ?>
			<p class="submit">
			<input type="hidden" name="fts_action" value="<?php echo $action; ?>" />
			<input type="submit" name="submit" class="button-primary" value="<?php echo $action; ?>" />
			</p>
			<?php } ?>
		</form>
	</div>
<?php
}

function fts_searchjoin( $join )
{
	if ( !get_option('fts_enabled') || (get_option('fts_enabled') != '1') ) {
		return;
	}

	global $wp, $wpdb;	
	$fts_mode = get_option("fts_mode"); // Possibile Modes: FREETEXT, CONTAINS

	if( is_search() && !is_admin()) 
		{
		$s = $wp->query_vars["s"];
		$s = str_replace("'", "''", $s);	
		if ($fts_mode == "CONTAINS")
		{
			$s = str_replace(" ", " AND ", $s);	
		}

		$join .= " INNER JOIN " . $fts_mode . "TABLE(" . $wpdb->posts . ", *, '" . $s . "') AS FTR ON " . $wpdb->posts . ".ID = FTR.[KEY]";
	}

	return $join;
}

function fts_orderby( $orderby )
{  
	if ( !get_option('fts_enabled') || (get_option('fts_enabled') != '1') ) {
		return;
	}

	if( is_search() && !is_admin()) 
	{
		$orderby = "FTR.[rank] DESC";
	}

	return $orderby;
}

function fts_where( $where )
{
	if ( !get_option('fts_enabled') || (get_option('fts_enabled') != '1') ) {
		return;
	}

	if( is_search() && !is_admin())  
	{  
		// Remove the unneeded LIKE
		$where = preg_replace("/\(.*.post.*\s+LIKE\s*\'[^\']+\'\s*\)*/", "1=1", $where );
	}
	
	return $where;
}
?>
